package com.ihr360.excel.handler;

import com.ihr360.excel.CatcheExcelI18nProps;
import com.ihr360.excel.annotation.ExcelCell;
import com.ihr360.excel.annotation.ExcelConfig;
import com.ihr360.excel.annotation.RowNumberField;
import com.ihr360.excel.cellstyle.ExcelCellStyle;
import com.ihr360.excel.cellstyle.ExcelCellStyleFactory;
import com.ihr360.excel.cellstyle.Ihr360CellStyle;
import com.ihr360.excel.constants.ExcelDefaultConfig;
import com.ihr360.excel.entity.CellComment;
import com.ihr360.excel.entity.ExcelI18nStrategyType;
import com.ihr360.excel.entity.ExportHeaderParams;
import com.ihr360.excel.entity.ExportParams;
import com.ihr360.excel.entity.ImportParams;
import com.ihr360.excel.entity.MergedRegionSpecification;
import com.ihr360.excel.exception.ExcelCanHandleException;
import com.ihr360.excel.exception.ExcelDateParseException;
import com.ihr360.excel.exception.ExcelException;
import com.ihr360.excel.exception.ExcellExceptionType;
import com.ihr360.excel.logs.ExcelCommonLog;
import com.ihr360.excel.logs.ExcelLogItem;
import com.ihr360.excel.logs.ExcelLogType;
import com.ihr360.excel.logs.ExcelLogs;
import com.ihr360.excel.specification.ColumnSpecification;
import com.ihr360.excel.specification.CommonSpecification;
import com.ihr360.excel.util.ExcelDateFormatUtil;
import com.ihr360.excel.util.ExcelDateParser;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ExecutionException;
import java.util.stream.Collectors;

import static com.ihr360.excel.handler.CellStyleHandler.getPoiCellStyle;
import static com.ihr360.excel.handler.CellStyleHandler.setSheetAutoAddWidthColumn;
import static com.ihr360.excel.handler.ValidatorHandler.checkBySpecificationType;
import static com.ihr360.excel.handler.ValidatorHandler.getColumnSpecifications;
import static com.ihr360.excel.handler.ValidatorHandler.requiredColumnNotExist;
import static com.ihr360.excel.handler.ValidatorHandler.validateCellData;
import static com.ihr360.excel.util.CommonExcelUtil.getCellValue;
import static com.ihr360.excel.util.CommonExcelUtil.isNullOrBlankStringCell;

public class DataHandler {

    private static Logger logger = LoggerFactory.getLogger(DataHandler.class);

    public static <T> void handleImportFlexfieldData(Map<String, Integer> fileHeaderIndexMap, Row row, List<Integer> configHeaderIndex, ImportParams<T> importParams, List<ExcelLogItem> rowLogs, T excelEntityVo) {

        Class<T> clazz = importParams.getImportType();
        List<Field> excelCellFields = getExcelCellField(clazz);
        List<ColumnSpecification> columnSpecifications = importParams.getColumnSpecifications();
        //列规则信息
        Map<String, ColumnSpecification> columnSpecificationMap = getColumnSpecifications(columnSpecifications, fileHeaderIndexMap.keySet());

        Field flexField = getFlexbleField(excelCellFields);
        if (flexField != null) {
            Map<Integer, String> flexFieldHeaderMap = new LinkedHashMap<>();

            fileHeaderIndexMap.forEach((header, index) -> {
                if (!configHeaderIndex.contains(index)) {
                    String flexHeader = flexFieldHeaderMap.get(index);
                    //存在重复表头
                    if (flexHeader != null) {
                        throw new ExcelCanHandleException(ExcellExceptionType.REPEATED_HEADER, new Object[]{index});
                    }
//
//                    for(String flexFieldHeader : flexFieldHeaderMap.values()){
//                        if(flexFieldHeader.equalsIgnoreCase(flexFieldHeaderMap.get(index))){
//                            throw new ExcelCanHandleException(ExcellExceptionType.REPEATED_HEADER, new Object[]{index});
//                        }
//                    }
                    flexFieldHeaderMap.put(index, header);
                }
            });
            //有序Map用来存放弹性字段
            Map<String, Object> flexFieldDataMap = new LinkedHashMap<>();

            if (MapUtils.isNotEmpty(flexFieldHeaderMap)) {

                for (Map.Entry<Integer, String> entry : flexFieldHeaderMap.entrySet()) {
                    Integer index = entry.getKey();
                    String header = entry.getValue();
                    Cell cell = row.getCell(index);

                    ColumnSpecification specification = columnSpecificationMap.get(header);
                    if (specification != null && specification.getCellType() != null) {
                        Class type = specification.getCellType();
                        boolean validType = true;
                        if (cell != null) {
                            validType = checkBySpecificationType(type, cell);
                        }
                        if (!validType) {
                            rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.ROW_COLUMN_FIELD_DATA_TYPE_ERR, new String[]{row.getRowNum() + 1 + "", header}, index));
                            continue;
                        }
                        try {
                            addCellDataToMap(flexFieldDataMap, header, cell, type);
                        } catch (ParseException e) {
                            rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.ROW_COLUMN_FIELD_DATA_TYPE_ERR, new String[]{row.getRowNum() + 1 + "", header}, index));
                            continue;
                        } catch (ExcelDateParseException | IllegalArgumentException e) {
                            rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.ROW_COLUMN_FIELD_DATA_TYPE_ERR, new String[]{row.getRowNum() + 1 + "", header}, index));
                            continue;
                        }

                    } else {
                        try {
                            addCellDataToMap(flexFieldDataMap, header, cell, null);
                        } catch (ParseException | IllegalArgumentException e) {
                            rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.ROW_COLUMN_FIELD_DATA_TYPE_ERR, new String[]{row.getRowNum() + 1 + "", header}, index));
                        }
                    }


                }
                setEntityFieldValue(clazz, excelEntityVo, flexField, flexFieldDataMap);
            }

        }


    }

    public static <T> void setEntityFieldValue(Class<T> clazz, T excelEntityVo, Field flexField, Object fieldValue) {
        flexField.setAccessible(true);
        try {
            flexField.set(excelEntityVo, fieldValue);
        } catch (IllegalAccessException e) {
            throw new ExcelException(MessageFormat.format("Can not set a value {0} for Object: {1} - field: {2}", fieldValue, clazz.getSimpleName(), flexField.getName()), e);
        }
    }

    public static void addCellDataToMap(Map<String, Object> flexFieldDataMap, String header, Cell cell, Class specificationType) throws ParseException {
        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            flexFieldDataMap.put(header, null);
        } else {
            Object value = getCellValue(cell, null, specificationType);

            flexFieldDataMap.put(header, value);

        }
    }

    public static Field getFlexbleField(List<Field> excelCellFields) {
        Field flexField = null;
        List<Field> flexbleFields = excelCellFields.stream()
                .filter(f -> isFlexbleField(f))
                .collect(Collectors.toList());
        if (flexbleFields.size() > 1) {
            throw new ExcelException("Each object can only have one flexible field，the current object has " + flexbleFields.size());
        } else if (flexbleFields.size() == 1) {
            flexField = flexbleFields.get(0);
        }
        return flexField;
    }

    public static boolean isFlexbleField(Field f) {
        return f.getAnnotation(ExcelCell.class) != null && f.getAnnotation(ExcelCell.class).flexibleField();
    }

    public static <T> T handleImportExcelRowToJavabean(ImportParams<T> importParams, Map<String, Integer> fileHeaderIndexMap, List<ExcelLogItem> rowLogs, Row row) {

        Class<T> clazz = importParams.getImportType();
        Map<String, List<String>> importHeader = importParams.getImportHeader();
        ExcelConfig excelConfig = clazz.getAnnotation(ExcelConfig.class);

        T excelEntityVo = getNewInstance(clazz);

        List<Field> excelCellFields = getExcelCellField(clazz);
        //从传入的表头或者根据i18n策略获得的表头在Excel中对应的列的集合
        List<Integer> configHeaderIndex = new ArrayList<>();

        for (Field field : excelCellFields) {
            RowNumberField rowNumberField = field.getAnnotation(RowNumberField.class);
            if (rowNumberField != null) {
                setEntityFieldValue(clazz, excelEntityVo, field, row.getRowNum() + 1 + "");
                continue;
            }

            String importHeaderKey = field.getAnnotation(ExcelCell.class).headerKey();
            if (StringUtils.isBlank(importHeaderKey)) {
                continue;
            }
            //通过国际化策略得到或者传入的表头
            List<String> configHeaders = getI18nHeadersByStrategy(clazz.getAnnotation(ExcelConfig.class), importHeaderKey);
            if (CollectionUtils.isEmpty(configHeaders) && MapUtils.isNotEmpty(importHeader)) {
                configHeaders = importHeader.get(importHeaderKey);
                if (CollectionUtils.isEmpty(configHeaders)) {
                    //输入表头和国际化策略取的表头均无此表头时忽略不导入
                    continue;
                }
            }

            String currentHeaderName = "";
            Integer cellIndex = null;
            for (String header : configHeaders) {
                cellIndex = fileHeaderIndexMap.get(header);
                if (cellIndex != null) {
                    currentHeaderName = header;
                    break;
                }
            }

            if (requiredColumnNotExist(rowLogs, field, cellIndex)) {
                continue;
            }

            configHeaderIndex.add(cellIndex);
            Cell cell = row.getCell(cellIndex);

            boolean valid = validateCellData(cell, field, currentHeaderName, rowLogs);

            if (!valid) {
                continue;
            }

            Object excelValueObj = null;
            try {
                excelValueObj = getExcelValueObj(field, cell, excelConfig);
            } catch (ParseException e) {
                rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.COLUMN_CON_NOT_CONVERT_TO_DATE, new String[]{currentHeaderName}));
                continue;
            } catch (NumberFormatException e) {
                rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.COLUMN_FIELD_DATA_TYPE_ERR, new String[]{currentHeaderName}));
                continue;
            }
            if (excelValueObj == null) {
                continue;
            }

            setEntityFieldValue(clazz, excelEntityVo, field, excelValueObj);
        }
        handleImportFlexfieldData(fileHeaderIndexMap, row, configHeaderIndex, importParams, rowLogs, excelEntityVo);

        return excelEntityVo;
    }

    public static <T> T getNewInstance(Class<T> clazz) {
        T excelEntityVo = null;
        try {
            excelEntityVo = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            throw new ExcelException(MessageFormat.format("can not instance class:{0}", clazz.getSimpleName()), e);
        }
        return excelEntityVo;
    }


    /**
     * 根据字段类型及excelConfig获取单元格value
     *
     * @param field
     * @param cell
     * @param excelConfig
     * @return
     * @throws ParseException
     */
    public static Object getExcelValueObj(Field field, Cell cell, ExcelConfig excelConfig) throws ParseException {
        Object excelValueObj = null;
        if (cell == null || StringUtils.isBlank(String.valueOf(cell))) {
            return excelValueObj;
        }

        Object cellValue = getCellValue(cell, excelConfig, null);
        // String类型的日期转换
//        if (Date.class == field.getType() && cell.getCellTypeEnum() == CellType.STRING) {
        if (Date.class == field.getType() && cell.getCellType() == Cell.CELL_TYPE_STRING) {
            excelValueObj = ExcelDateParser.getDate(String.valueOf(cellValue));
//        } else if (Timestamp.class == field.getType() && cell.getCellTypeEnum() == CellType.STRING) {
        } else if (Timestamp.class == field.getType() && cell.getCellType() == Cell.CELL_TYPE_STRING) {
            excelValueObj = ExcelDateParser.getTimestamp(String.valueOf(cellValue));
        } else if (Integer.class == field.getType()) {
            return Integer.parseInt(String.valueOf(cellValue));
        } else if (Double.class == field.getType()) {
            return Double.parseDouble(String.valueOf(cellValue));
        } else if (Long.class == field.getType()) {
            return Long.parseLong(String.valueOf(cellValue));
        } else if (String.class == field.getType()) {
            return String.valueOf(cellValue);
        } else {
            excelValueObj = getCellValue(cell, excelConfig, null);
            // 处理特殊情况,excel的value为String,且bean中为其他,且defaultValue不为空,那就=defaultValue
            ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
            if (excelValueObj instanceof String && !(field.getType() == String.class) && StringUtils.isNotBlank(annoCell.defaultValue())) {
                excelValueObj = annoCell.defaultValue();
            }
        }
        return excelValueObj;
    }

    public static Map<String, Object> handleExcelRowToMap(Map<String, Integer> fileHeaderIndexMap, Row row, List<ExcelLogItem> rowLogs, List<ColumnSpecification> columnSpecifications) {
        Map<String, Object> map = new LinkedHashMap<>();
        // 判空

        for (Map.Entry<String, Integer> entry : fileHeaderIndexMap.entrySet()) {
            String fileHeaderName = entry.getKey();
            Integer headerIndex = fileHeaderIndexMap.get(fileHeaderName);
            Cell cell = row.getCell(headerIndex);

            //列规则信息
            Map<String, ColumnSpecification> columnSpecificationMap = getColumnSpecifications(columnSpecifications, fileHeaderIndexMap.keySet());
            ColumnSpecification specification = columnSpecificationMap.get(fileHeaderName);
            boolean checkSpecification = specification != null && specification.getCellType() != null;
            if (checkSpecification) {
                if (isNullOrBlankStringCell(cell) && !specification.isAllowNull()) {
                    rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.COLUMN_DATA_REQUIRED, new String[]{fileHeaderName}, headerIndex));
                    continue;
                } else if (isNullOrBlankStringCell(cell) && specification.isAllowNull()) {

                    try {
                        addCellDataToMap(map, fileHeaderName, cell, specification.getCellType());
                    } catch (ParseException e) {
                        rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.ROW_COLUMN_FIELD_DATA_TYPE_ERR, new String[]{row.getRowNum() + 1 + "", fileHeaderName}, headerIndex));
                    }
                    continue;
                }

                Class type = specification.getCellType();
                boolean validType = checkBySpecificationType(type, cell);
                if (!validType) {
                    rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.ROW_COLUMN_FIELD_DATA_TYPE_ERR, new String[]{row.getRowNum() + 1 + "", fileHeaderName}, headerIndex));
                    continue;
                }
                try {
                    addCellDataToMap(map, fileHeaderName, cell, type);
                } catch (ParseException | IllegalArgumentException e) {
                    rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.ROW_COLUMN_FIELD_DATA_TYPE_ERR, new String[]{row.getRowNum() + 1 + "", fileHeaderName}, headerIndex));
                    continue;
                }

            } else {
                try {
                    addCellDataToMap(map, fileHeaderName, cell, null);
                } catch (ParseException | IllegalArgumentException e) {
                    rowLogs.add(ExcelLogItem.createExcelItem(ExcelLogType.ROW_COLUMN_FIELD_DATA_TYPE_ERR, new String[]{row.getRowNum() + 1 + "", fileHeaderName}, headerIndex));
                }
            }
        }

        return map;
    }


    public static Sheet getSheetFromWorkbook(InputStream inputStream, ExcelLogs logs) {
        Sheet sheet = null;
        ExcelCommonLog commonLog = logs.getExcelCommonLog();
        List<ExcelLogItem> excelLogItems = commonLog.getExcelLogItems();
        if (excelLogItems == null) {
            excelLogItems = new ArrayList<>();
            commonLog.setExcelLogItems(excelLogItems);
        }
        try (Workbook workBook = WorkbookFactory.create(inputStream)) {
            //支持.xls和.xlsx
            sheet = workBook.getSheetAt(0);
        } catch (EncryptedDocumentException e) {
            logger.info(e.getMessage());
            excelLogItems.add(ExcelLogItem.createExcelItem(ExcelLogType.EXCEL_COMMON_ENCRYPTED, null));
        } catch (InvalidFormatException e) {
            logger.info(e.getMessage());
            excelLogItems.add(ExcelLogItem.createExcelItem(ExcelLogType.EXCEL_COMMON_FORMAT_ENCRYPTED, null));
        } catch (Exception e) {
            logger.error("load excel file error", e);
            excelLogItems.add(ExcelLogItem.createExcelItem(ExcelLogType.EXCEL_COMMON_COMMON, null));

        }
        return sheet;
    }


    public static List<String> getI18nHeadersByStrategy(ExcelConfig excelConfig, String importHeaderKey) {
        List<String> i18nHeaders = new ArrayList<>();
        if (excelConfig != null) {
            ExcelI18nStrategyType excelI18nStrategyType = excelConfig.i18nStrategy();
            switch (excelI18nStrategyType) {
                case EXCEL_I18N_STRATEGY_PROPS:
                    String propsFileName = excelConfig.propsFileName();
                    if (StringUtils.isNotBlank(propsFileName)) {
                        try {
                            i18nHeaders = CatcheExcelI18nProps.getI18SortedHeaders(importHeaderKey, propsFileName);
                        } catch (ExecutionException e) {
                            logger.error("根据词条key从配置文件中获取表头时失败，headerKey：" + importHeaderKey + ",propsFileName:" + propsFileName);
                            throw new ExcelException("Error reading Excel configuration file", e.getCause());
                        }
                    }
                    break;
                default:
            }
        }
        return i18nHeaders;
    }

    /**
     * row转Map<列名，列index>
     *
     * @param row
     * @return
     */
    public static Map<String, Integer> convertRowToHeaderMap(Row row, boolean checkRepeat) {
        Map<String, Integer> fileMap = new LinkedHashMap<>();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            String value = StringUtils.EMPTY;
            String valueOld = StringUtils.EMPTY;
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:

                    if (DateUtil.isCellDateFormatted(cell)) {// 判断是日期类型
                        throw new ExcelCanHandleException(ExcellExceptionType.HEADER_DATE_FIELD_NOTSUPPORT);
                    } else {
                        value = NumberToTextConverter.toText(cell.getNumericCellValue());
                        valueOld = value;
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    //value = cell.getStringCellValue().replace(" ", "");
                    value = cell.getStringCellValue();
                    valueOld = value;
                    break;
                case Cell.CELL_TYPE_BLANK:
                    continue;
                default:
                    throw new ExcelCanHandleException(ExcellExceptionType.HEADER_DATA_TYPE_NOTSUPPORT);
            }
            /*if (fileMap.get(value) != null && checkRepeat) {
                throw new ExcelCanHandleException(ExcellExceptionType.REPEATED_HEADER, new Object[]{value});
            }*/
            //存在重复表头
            for (String file : fileMap.keySet()) {
                if (file.replace(" ", "").equalsIgnoreCase(value.replace(" ", "")) && checkRepeat) {
                    throw new ExcelCanHandleException(ExcellExceptionType.REPEATED_HEADER, new Object[]{valueOld});
                }
            }
            fileMap.put(valueOld, cell.getColumnIndex());
        }
        return fileMap;
    }

    public static List<Field> getExcelCellField(Class<?> clazz) {
        Field[] fieldsArr = clazz.getDeclaredFields();

        Class superClazz = clazz.getSuperclass();
        if (superClazz != null) {
            Field[] superFieldsArr = superClazz.getDeclaredFields();
            fieldsArr = ArrayUtils.addAll(fieldsArr, superFieldsArr);
        }
        if (ArrayUtils.isEmpty(fieldsArr)) {
            return new ArrayList<>();
        }
        return Arrays.stream(fieldsArr)
                .filter(f -> (f.getAnnotation(ExcelCell.class) != null || f.getAnnotation(RowNumberField.class) != null))
                .collect(Collectors.toList());
    }

    /**
     * 每个sheet的写入
     *
     * @param sheet    页签
     * @param workbook
     */
    public static <T> void write2Sheet(Sheet sheet, ExportParams<T> exportParams, Workbook workbook) {

        Map<String, String> headers = exportParams.getHeaderMap();

        Collection<T> datas = exportParams.getRowDatas();

        // 标题行转中文 headers是有序的map，产生的keySet也是有序的
        List<String> headerKeys = new ArrayList<>(headers.keySet());
        Map<String, Font> fontMap = new HashedMap();
        Map<Integer, String> datePatternIndexMap = new HashMap<>();

        List<MergedRegionSpecification> headerMergedReginSpcifications = new ArrayList<>();
        List<MergedRegionSpecification> mergedRegionSpecifications = exportParams.getMergedRegionSpecifications();
        if (CollectionUtils.isNotEmpty(mergedRegionSpecifications)) {
            headerMergedReginSpcifications = mergedRegionSpecifications.stream()
                    .filter(MergedRegionSpecification::getIsHeader).collect(Collectors.toList());
        }
        if (CollectionUtils.isNotEmpty(headerMergedReginSpcifications)) {
            Ihr360CellStyle defaultHeaderCellStyle = ExcelCellStyleFactory.createDefaultHeaderCellStyle();
            Map<String, CellStyle> excelCellStyleMap = new HashMap<>();
            Map<String, ExcelCellStyle> headerStyleMap = exportParams.getHeaderStyleMap();


            Set<Integer> headerRows = new HashSet<>();
            for (MergedRegionSpecification specification : headerMergedReginSpcifications) {
                ExportHeaderParams exportHeaderParams = specification.getExportHeaderParams();
                if (exportHeaderParams == null) {
                    throw new ExcelException("ExportHeaderParams can not be null!");
                }
                Map<String, String> mergedHeaderMap = exportHeaderParams.getHeaderMap();
                if (MapUtils.isEmpty(mergedHeaderMap)) {
                    throw new ExcelException("mergedHeaderMap can not be empty!");
                }

                Row row = sheet.createRow(specification.getRowNum());
                row.setHeightInPoints(ExcelDefaultConfig.DEFAULT_ROW_HEADER_HEIGHT_INPOINT);
                headerRows.add(specification.getRowNum());

                exportParams.setMergedHeaderMap(mergedHeaderMap);
                datePatternIndexMap = handleExportHeader(workbook, row, fontMap, exportParams, exportHeaderParams.getStartIndex(), sheet);

                //合并单元格
                List<int[]> specifiCationParams = specification.getSpecifiCationParams();
                for (int[] params : specifiCationParams) {
                    sheet.addMergedRegion(new CellRangeAddress(params[0], params[1], params[2], params[3]));
                }
            }
            //合并单元格后再次处理单元格样式
            if (CollectionUtils.isNotEmpty(headerRows)) {
                for (Integer rowNum : headerRows) {
                    Row row = sheet.getRow(rowNum);
                    for (String headerKey : headerKeys) {
                        CellStyle poiCellStyle = getPoiCellStyle(workbook, fontMap, defaultHeaderCellStyle, excelCellStyleMap, headerStyleMap, headerKey);
                        Cell cell = row.getCell(headerKey.indexOf(headerKey));
                        if (cell == null) {
                            continue;
                        }
                        cell.setCellStyle(poiCellStyle);
                    }
                }
            }

        } else {
            // 产生表格标题行
            Row row = sheet.createRow(0);
            row.setHeightInPoints(ExcelDefaultConfig.DEFAULT_ROW_HEADER_HEIGHT_INPOINT);
            datePatternIndexMap = handleExportHeader(workbook, row, fontMap, exportParams, 0, sheet);
        }


        CellStyle textCellStyle = ExcelCellStyleFactory.createdDefaultTextCellStyle(workbook);
        // 遍历集合数据，产生数据行
        if (CollectionUtils.isEmpty(datas)) {

            handleDropDownList(exportParams, workbook, sheet, 1, 1000);
            for (int i = 0; i < headers.size(); i++) {
                setSheetAutoAddWidthColumn(sheet, i);
                sheet.setDefaultColumnStyle(i, textCellStyle);
            }
            return;
        }
        handleDropDownList(exportParams, workbook, sheet, 1, datas.size() + 10);


        Iterator<T> datasIt = datas.iterator();
        int index = 0;
        while (datasIt.hasNext()) {
            index++;
            Row row = sheet.getRow(index);
            if (row == null) {
                row = sheet.createRow(index);
            }
            T rowData = datasIt.next();
            try {
                if (rowData instanceof List) {
                    @SuppressWarnings("unchecked")
                    List<Object> cellDatas = (List<Object>) rowData;
                    int cellNum = 0;
                    //遍历列名
                    for (int i = 0; i < cellDatas.size(); i++) {
                        Cell cell = row.createCell(i);
                        String datePattern = datePatternIndexMap.get(i);
                        if (StringUtils.isBlank(datePattern)) {
                            datePattern = ExcelDefaultConfig.DEFAULT_OUTPUT_DATE_PATTERN;
                        }
                        setCellValue(cell, cellDatas.get(i), datePattern, null, textCellStyle);
                    }

                } else {
                    List<Field> fields = getExcelCellField(rowData.getClass());
                    Map<String, Field> fieldsMap = new HashedMap();
                    fields.forEach(field -> {
                        ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
                        if (excelCell != null) {
                            String headerKey = excelCell.headerKey();
                            if (StringUtils.isNotBlank(headerKey)) {
                                fieldsMap.put(headerKey, field);
                            }
                        }

                    });
                    for (int i = 0; i < headerKeys.size(); i++) {
                        Cell cell = row.createCell(i);
                        Field field = fieldsMap.get(headerKeys.get(i));
                        field.setAccessible(true);
                        Object value = field.get(rowData);
                        String datePattern = datePatternIndexMap.get(i);
                        if (StringUtils.isBlank(datePattern)) {
                            datePattern = ExcelDefaultConfig.DEFAULT_OUTPUT_DATE_PATTERN;
                        }
                        setCellValue(cell, value, datePattern, field, textCellStyle);
                    }
                }
            } catch (Exception e) {
                logger.error(e.toString(), e);
            }
        }
        // 设定自动宽度
        for (int i = 0; i < headers.size(); i++) {
            setSheetAutoAddWidthColumn(sheet, i);
            sheet.setDefaultColumnStyle(i, textCellStyle);
        }

    }

    public static Map<Integer, String> handleExportHeader(Workbook workbook, Row row, Map<String, Font> fontMap, ExportParams exportParams, int startIndex, Sheet sheet) {
        Map<Integer, String> datePatternIndexMap = new HashMap<>();
        Map<String, String> datePatternMap = exportParams.getDatePatternMap();

        Ihr360CellStyle defaultHeaderCellStyle = ExcelCellStyleFactory.createDefaultHeaderCellStyle();
        Map<String, CellStyle> excelCellStyleMap = new HashMap<>();
        Map<String, String> headers = exportParams.getHeaderMap();

        if (MapUtils.isNotEmpty(exportParams.getMergedHeaderMap())) {
            headers = exportParams.getMergedHeaderMap();
        }

        List<String> headerKeys = new ArrayList<>(headers.keySet());
        Map<String, ExcelCellStyle> headerStyleMap = exportParams.getHeaderStyleMap();

        Map<String, CellComment> headerCommentMap = exportParams.getHeaderCommentMap();

        for (int i = 0; i < headerKeys.size(); i++) {
            int index = i + startIndex;
            Cell cell = row.createCell(index);
            String headerKey = headerKeys.get(i);
            String headerName = headers.get(headerKey);

            HSSFRichTextString text = new HSSFRichTextString(headerName);
            cell.setCellValue(text);

            if (MapUtils.isNotEmpty(datePatternMap)) {
                String pattern = datePatternMap.get(headerKey);
                if (StringUtils.isNotBlank(pattern)) {
                    datePatternIndexMap.put(index, pattern);
                }
            }
            setHeaderComment(sheet, headerCommentMap, cell, headerKey);

            CellStyle poiCellStyle = getPoiCellStyle(workbook, fontMap, defaultHeaderCellStyle, excelCellStyleMap, headerStyleMap, headerKey);
            cell.setCellStyle(poiCellStyle);
        }
        return datePatternIndexMap;
    }

    public static void setHeaderComment(Sheet sheet, Map<String, CellComment> headerCommentMap, Cell cell, String headerKey) {
        if (MapUtils.isNotEmpty(headerCommentMap)) {
            CellComment cellComment = headerCommentMap.get(headerKey);
            if (cellComment != null) {
                Drawing patr = sheet.createDrawingPatriarch();
                int[] params = cellComment.getAnchorParams();
                ClientAnchor anchor = patr.createAnchor(params[0], params[1], params[2], params[3], params[4], params[5], params[6], params[7]);
                Comment comment = patr.createCellComment(anchor);
                if (StringUtils.isNotBlank(cellComment.getContentString())) {
                    //TODO 目前导出置支持到ＨＳＳＦ格式
                    comment.setString(new HSSFRichTextString(cellComment.getContentString()));
                }
                if (StringUtils.isNotBlank(cellComment.getAuthor())) {
                    comment.setAuthor(cellComment.getAuthor());
                }
                comment.setVisible(cellComment.isVisible());
                cell.setCellComment(comment);
            }
        }
    }

    public static void setCellValue(Cell cell, Object value, String pattern, Field field, CellStyle textCellStyle) {
        String textValue = null;
        if (value instanceof Integer) {
            int intValue = (Integer) value;
            cell.setCellValue(intValue);
        } else if (value instanceof Float) {
            float fValue = (Float) value;
            cell.setCellValue(fValue);
        } else if (value instanceof Double) {
            double dValue = (Double) value;
            cell.setCellValue(dValue);
        } else if (value instanceof Long) {
            long longValue = (Long) value;
            cell.setCellValue(longValue);
        } else if (value instanceof Boolean) {
            boolean bValue = (Boolean) value;
            cell.setCellValue(bValue);
        } else if (value instanceof Date) {
            Date date = (Date) value;
            textValue = ExcelDateFormatUtil.formatDate(pattern, date.getTime());
        }
        //TODO 暂不支持数组导出
        /*else if (value instanceof String[]) {
            String[] strArr = (String[]) value;
            for (int j = 0; j < strArr.length; j++) {
                String str = strArr[j];
                cell.setCellValue(str);
                if (j != strArr.length - 1) {
                    cellNum++;
                    cell = row.createCell(cellNum);
                }
            }
        } else if (value instanceof Double[]) {
            Double[] douArr = (Double[]) value;
            for (int j = 0; j < douArr.length; j++) {
                Double val = douArr[j];
                // 值不为空则set Value
                if (val != null) {
                    cell.setCellValue(val);
                }

                if (j != douArr.length - 1) {
                    cellNum++;
                    cell = row.createCell(cellNum);
                }
            }
        }*/
        else {
            // 其它数据类型都当作字符串简单处理
            String defaultStr = StringUtils.EMPTY;
            if (field != null) {
                ExcelCell anno = field.getAnnotation(ExcelCell.class);
                if (anno != null) {
                    defaultStr = anno.defaultValue();
                }
            }
            textValue = value == null ? defaultStr : value.toString();
        }
        if (textValue != null) {
            HSSFRichTextString richString = new HSSFRichTextString(textValue);
            cell.setCellStyle(textCellStyle);
            cell.setCellValue(richString);
        }
    }

    /**
     * @param wb               HSSFWorkbook对象
     * @param realSheet        需要操作的sheet对象
     * @param datas            下拉的列表数据
     * @param startRow         开始行
     * @param endRow           结束行
     * @param startCol         开始列
     * @param endCol           结束列
     * @param hiddenSheetName  隐藏的sheet名
     * @param hiddenSheetIndex 隐藏的sheet索引 考虑到有多个列绑定下拉列表
     * @return
     * @throws Exception
     */
    public static HSSFWorkbook dropDownList2003(Workbook wb, Sheet realSheet, String[] datas, int startRow, int endRow,
                                                int startCol, int endCol, String hiddenSheetName, int hiddenSheetIndex) {

        HSSFWorkbook workbook = (HSSFWorkbook) wb;
        // 创建一个数据源sheet
        HSSFSheet hidden = workbook.createSheet(hiddenSheetName);
        // 数据源sheet页不显示
        workbook.setSheetHidden(hiddenSheetIndex, true);
        // 将下拉列表的数据放在数据源sheet上
        HSSFRow row = null;
        HSSFCell cell = null;
        for (int i = 0, length = datas.length; i < length; i++) {
            row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(datas[i]);
        }
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheetName + "!$A$1:$A" + datas.length);
        CellRangeAddressList addressList = null;
        HSSFDataValidation validation = null;
        row = null;
        cell = null;
        // 单元格样式
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // 循环指定单元格下拉数据
        for (int i = startRow; i <= endRow; i++) {
            row = (HSSFRow) realSheet.createRow(i);
            cell = row.createCell(startCol);
            cell.setCellStyle(style);
            addressList = new CellRangeAddressList(i, i, startCol, endCol);
            validation = new HSSFDataValidation(addressList, constraint);
            realSheet.addValidationData(validation);
        }

        return workbook;
    }

    public static <T> Workbook handleDropDownList(ExportParams<T> exportParams, Workbook workbook, Sheet sheet, int startRow, int endRow) {
        Map<String, List<String>> dropDownsMap = exportParams.getDropDownsMap();
        Map<String, String> headers = exportParams.getHeaderMap();
        if (MapUtils.isNotEmpty(dropDownsMap)) {
            //linkHashMap的有序表头
            Set<String> headerKeys = headers.keySet();
            List<String> headerKeyList = new ArrayList<>(headerKeys);
            Set<String> dropHeaderKeys = dropDownsMap.keySet();
            int i = 1;
            for (String headerKey : dropHeaderKeys) {
                int columnIndex = headerKeyList.indexOf(headerKey);
                if (columnIndex < 0) {
                    continue;
                }
                List<String> dropList = dropDownsMap.get(headerKey);
                if (CollectionUtils.isEmpty(dropList)) {
                    continue;
                }
                String[] dropArray = new String[dropList.size()];

                for (int j = 0; j < dropList.size(); j++) {
                    dropArray[j] = dropList.get(j);
                }

                String hiddenSheetName = String.join("_", "hidden_sheet", i + "");
                workbook = dropDownList2003(workbook, sheet, dropArray, startRow, endRow, columnIndex, columnIndex, hiddenSheetName, i);
                i++;
            }
        }
        return workbook;
    }

    public static <T> void handleCommonSpecification(ImportParams<T> importParams, Row row, Map<String, Object> map) {
        CommonSpecification commonSpecification = importParams.getCommonSpecification();
        if (commonSpecification != null && commonSpecification.isShowRowNum()) {
            String rowNumKey = StringUtils.isEmpty(commonSpecification.getRowNumKey())
                    ? ExcelDefaultConfig.COMMON_SPECIFICATION_ROWNUM
                    : commonSpecification.getRowNumKey();
            map.put(rowNumKey, row.getRowNum() + 1);
        }
    }
}
